package com.gmss.crawler.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.gmss.crawler.enums.AcademicianEnum;
import com.gmss.crawler.module.company.domain.Company;
import com.gmss.crawler.module.company.service.ICompanyService;
import com.gmss.crawler.module.paper.domain.Paper;
import com.gmss.crawler.module.person.domain.PersonInfo;
import com.gmss.crawler.module.person.service.IPersonInfoService;
import com.gmss.crawler.module.test.domain.PersonInfoTopy;
import com.gmss.crawler.utils.DateUtils;
import com.gmss.crawler.utils.RegexUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

@Slf4j
@Service
public class ParseExcelService {

    @Resource
    private IPersonInfoService iPersonInfoService;

    @Resource
    private ICompanyService iCompanyService;

    public List<PersonInfo> parseExcelSaveDatabase(Integer start, Integer end) throws Exception {
        List<PersonInfo> infoList = new ArrayList<>();
        //根据路径获取这个操作excel的实例
        org.springframework.core.io.Resource resource = new ClassPathResource("/htmlTest/ac-院士数据.xlsx");
        String filePath = resource.getFile().getAbsolutePath();
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(filePath)));
        //根据页面index 获取sheet页
        XSSFSheet sheet = wb.getSheetAt(0);        //实体类集合
        Row row = null;        //循环sesheet页中数据从第二行开始，第一行是标题
        for (int i = 2; i < end; i++) {            //获取每一行数据
            row = sheet.getRow(i);

            PersonInfo personInfo = iPersonInfoService.getById(Integer.valueOf((int) row.getCell(0).getNumericCellValue()));
            if (null == personInfo) {
                throw new Exception("--------------personInfo is null---------------");
            }

            if (!StringUtils.equals(personInfo.getName(), row.getCell(1).getStringCellValue())) {
                throw new Exception("姓名不同，数据库姓名：" + personInfo.getName() + "   ---------excel姓名：{}" + row.getCell(1).getStringCellValue());
            }

            if (row.getCell(2) != null && StringUtils.isNotBlank(row.getCell(2).getStringCellValue())) {
                personInfo.setForeignName(row.getCell(2).getStringCellValue());
            }
            if (personInfo.getCompanyId() == null) {
                if (row.getCell(3) != null && StringUtils.isNotBlank(row.getCell(3).getStringCellValue())) {
                    LambdaQueryWrapper<Company> queryWrapper = new LambdaQueryWrapper<>();
                    //queryWrapper.exists("select id from company where company_name =" + row.getCell(3).getStringCellValue());
                    queryWrapper.eq(Company::getCompanyName, row.getCell(3).getStringCellValue());
                    Company one = iCompanyService.getOne(queryWrapper);
                    if (one != null) {
                        personInfo.setCompanyId(one.getId());
                    } else {
                        Company company = new Company();
                        company.setCompanyName(row.getCell(3).getStringCellValue());
                        company.setAddress(row.getCell(4).getStringCellValue());
                        company.setCompanyType(CasCrawlerService.getCompanyType(row.getCell(3).getStringCellValue()));
                        iCompanyService.save(company);
                        personInfo.setCompanyId(company.getId());
                    }
                }
            }
            if (row.getCell(6) != null) {
                CellType cellType = row.getCell(6).getCellTypeEnum();
                if (null != cellType && cellType.getCode() == 1) {
                    try {
                        String ateOfDeath = row.getCell(6).getStringCellValue();
                        ateOfDeath = DateUtils.handleDate(ateOfDeath);
                        personInfo.setDateOfDeath(ateOfDeath);
                    } catch (Exception e) {
                        personInfo.setDateOfDeath(row.getCell(6).getStringCellValue());
                    }
                } else {
                    Date date = HSSFDateUtil.getJavaDate(row.getCell(6).getNumericCellValue());
                    personInfo.setDateOfDeath(DateUtils.dateToString(date, "yyyy-MM-dd"));
                }
            }
            row.getCell(7).setCellType(CellType.STRING);
            if (row.getCell(7) != null && StringUtils.isNotBlank(row.getCell(7).getStringCellValue())) {
                personInfo.setNation(row.getCell(7).getStringCellValue());
            }

            if (row.getCell(8) != null && StringUtils.isNotBlank(row.getCell(8).getStringCellValue())) {
                personInfo.setNationality(row.getCell(8).getStringCellValue());
            }
            if (row.getCell(9) != null) {


                CellType cellType = row.getCell(9).getCellTypeEnum();
                if (null != cellType && cellType.getCode() == 1) {
                    try {
                        String ateOfDeath = row.getCell(9).getStringCellValue();
                        ateOfDeath = DateUtils.handleDate(ateOfDeath);
                        personInfo.setBirthday(ateOfDeath);
                    } catch (Exception e) {
                        personInfo.setBirthday(row.getCell(9).getStringCellValue());
                    }
                } else {
                    Date date = HSSFDateUtil.getJavaDate(row.getCell(9).getNumericCellValue());
                    personInfo.setBirthday(DateUtils.dateToString(date, "yyyy-MM-dd"));
                }


            }
            if (row.getCell(10) != null && StringUtils.isNotBlank(row.getCell(10).getStringCellValue())) {
                personInfo.setBirthplace(row.getCell(10).getStringCellValue());
            }
            if (row.getCell(11) != null && StringUtils.isNotBlank(row.getCell(11).getStringCellValue())) {
                personInfo.setSchool(row.getCell(11).getStringCellValue());
            }
            if (row.getCell(12) != null && StringUtils.isNotBlank(row.getCell(12).getStringCellValue())) {
                personInfo.setDepartment(row.getCell(12).getStringCellValue());
            }
            if (row.getCell(13) != null && StringUtils.isNotBlank(row.getCell(13).getStringCellValue())) {
                personInfo.setMajor(row.getCell(13).getStringCellValue());
            }
            if (row.getCell(14) != null && StringUtils.isNotBlank(row.getCell(14).getStringCellValue())) {
                personInfo.setPersonTitle(row.getCell(14).getStringCellValue());
            }
            if (row.getCell(15) != null && StringUtils.isNotBlank(row.getCell(15).getStringCellValue())) {
                personInfo.setAcademicDegree(row.getCell(15).getStringCellValue());
            }
            if (row.getCell(16) != null && StringUtils.isNotBlank(row.getCell(16).getStringCellValue())) {
                personInfo.setAchievement(row.getCell(16).getStringCellValue());
            }
            if (row.getCell(17) != null && StringUtils.isNotBlank(row.getCell(17).getStringCellValue())) {
                String describeData = row.getCell(17).getStringCellValue();
                RegexUtils.replaceData(describeData, RegexUtils.ALL_ZHONG_REGEX, "");
                personInfo.setDescribeData(describeData);
            }
            personInfo.setUpdateTime(new Date());
            infoList.add(personInfo);
        }
        return infoList;
    }


    public List<PersonInfo> parseSchoolPersonExcel(String filePath) {
        List<PersonInfo> list = new ArrayList<>();
        File file = new File(filePath);
        if (!file.exists())
            return list;
        try {
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet sheet = wb.getSheetAt(0);        //实体类集合
            Row row = null;        //循环sesheet页中数据从第二行开始，第一行是标题
            int end = sheet.getPhysicalNumberOfRows();
            Set<String> repeatNameSet = new HashSet<>();
            for (int i = 1; i < end; i++) {
                //获取每一行数据
                row = sheet.getRow(i);
                short startCellNum = row.getFirstCellNum();
//                short endCellNum = row.getLastCellNum();

                String companyName = "";
                String name = "";
                String duties = "";
                String personPath = "";
                String sex = "";
//                System.out.println(row.getCell(0));
//                System.out.println(row.getCell(1));
//                System.out.println(row.getCell(2));
//                System.out.println(row.getCell(3));
                if (row.getCell(0) != null && StringUtils.isNotBlank(row.getCell(0).getStringCellValue())) {
                    companyName = row.getCell(0).getStringCellValue();
                }
                if (row.getCell(1) != null && StringUtils.isNotBlank(row.getCell(1).getStringCellValue())) {
                    name = row.getCell(1).getStringCellValue();
                    if (StringUtils.contains(name, "（")) {
                        name = name.replaceAll("（", "(").replaceAll("）", ")");
                        sex = RegexUtils.getTextContent(name, RegexUtils.ALL_KUA_REGEX_TWO);
                        name = RegexUtils.replaceData(name, RegexUtils.ALL_KUA_REGEX, "");
                    }
                }
                if (row.getCell(2) != null && StringUtils.isNotBlank(row.getCell(2).getStringCellValue())) {
                    duties = row.getCell(2).getStringCellValue();
                    if (StringUtils.contains(duties, "[")) {
                        duties = RegexUtils.replaceData(duties, RegexUtils.ALL_ZHONG_REGEX, "");
                    }
                }
                if (row.getCell(3) != null && StringUtils.isNotBlank(row.getCell(3).getStringCellValue())) {
                    personPath = row.getCell(3).getStringCellValue();
                }

                if (StringUtils.isAnyBlank(companyName, name)) {
                    log.error("companyName or name is null :{}" , (""+row.getCell(0) + row.getCell(1) + row.getCell(2) + row.getCell(3)));
                    continue;
                }

                String repeatNameStr = companyName + name;
                if (checkRepeatName(list, repeatNameSet, companyName, name, duties, personPath, repeatNameStr))
                    continue;

                LambdaQueryWrapper<Company> queryWrapper = new LambdaQueryWrapper<>();
                queryWrapper.eq(Company::getCompanyName, companyName);
                queryWrapper.eq(Company::getCheckStatus, 1);
                queryWrapper.eq(Company::getDepartment, "教育部");
                Company company = iCompanyService.getOne(queryWrapper);
                PersonInfo personInfo = new PersonInfo().builder()
                        .companyId(company.getId())
                        .academicianTitle(AcademicianEnum.SCHOOL_LEADER.getCode())
                        .academicianTitleValue(duties)
                        .workCompany(companyName)
                        .name(name)
//                        .duties(duties)
                        .build();
                if (StringUtils.isNotBlank(sex)) {
                    personInfo.setSex(sex);
                }
                if (StringUtils.isNotBlank(personPath)) {
                    //[{"relatedLinksName":"AMiner","relatedLinksUrl":" https://www.aminer.cn/profile/545938e7dabfaeb0fe34c88b"}]
                    JSONObject jsonObject = new JSONObject();
                    jsonObject.put("relatedLinksName", "personInfoPath");
                    jsonObject.put("relatedLinksUrl", personPath);
                    JSONArray jsonArray = new JSONArray();
                    jsonArray.add(jsonObject);
                    personInfo.setRelatedLinks(JSON.toJSONString(jsonArray));
                }
                list.add(personInfo);
            }
        } catch (IOException e) {
            log.error("parseSchoolPersonExcel 解析校领导Excel异常:{}", e);
        }
        return list;
    }

    private boolean checkRepeatName(List<PersonInfo> list, Set<String> repeatNameSet, String companyName, String name, String duties, String personPath, String repeatNameStr) {
        if (repeatNameSet.contains(repeatNameStr)) {
            for(PersonInfo personInfo : list) {
                if (StringUtils.equals(personInfo.getWorkCompany() , companyName) && StringUtils.equals(personInfo.getName(), name)) {
                    String academicianTitleValue = personInfo.getAcademicianTitleValue() + "、" + duties;
                    personInfo.setAcademicianTitleValue(academicianTitleValue);
//                    String relateLinks =  personInfo.getRelatedLinks();
//                    if (StringUtils.isNotBlank(relateLinks)) {
//                        JSONArray jsonArray = JSON.parseArray(relateLinks);
//                        for (int j = 0 ; j <jsonArray.size(); j++) {
//                            JSONObject jsonObject = (JSONObject) jsonArray.get(j);
//                            if (jsonObject.containsKey("relatedLinksName") && StringUtils.equals(jsonObject.getString("relatedLinksName"), "personInfoPath") &&  StringUtils.equals(jsonObject.getString("relatedLinksUrl"), personPath)) {
//                                String academicianTitleValue = personInfo.getAcademicianTitleValue() + "、" + duties;
//                                personInfo.setAcademicianTitleValue(academicianTitleValue);
//                            }
//                        }
//                    }
                    return true;
                }
            }
        } else {
            repeatNameSet.add(repeatNameStr);
        }
        return false;
    }



    public static List<Paper> parsePaperExcel(String filePath, Long personId) {
        List<Paper> list = new ArrayList<>();
        File file = new File(filePath);
        if (!file.exists())
            return list;
        try {
            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet sheet = wb.getSheetAt(0);        //实体类集合
            Row row = null;        //循环sesheet页中数据从第二行开始，第一行是标题
            int end = sheet.getPhysicalNumberOfRows();
            Set<String> repeatNameSet = new HashSet<>();
            for (int i = 1; i < end; i++) {
                //获取每一行数据
                row = sheet.getRow(i);
                short startCellNum = row.getFirstCellNum();
//                short endCellNum = row.getLastCellNum();

                String title = "";
                String doi = "";
                Integer languageStandard = 1;
                String keyword = "";
                String firstAuthor = "";
                String otherAuthor = "";
                String periodical = "";
                String desc = "";
                String subject = "";
                String publicationDate = "";
                String instituteStandard = "";
                if (row.getCell(1) != null && StringUtils.isNotBlank(row.getCell(1).getStringCellValue())) {
                    title = row.getCell(1).getStringCellValue();
                }
                if (row.getCell(2) != null && StringUtils.isNotBlank(row.getCell(2).getStringCellValue())) {
                    doi = row.getCell(2).getStringCellValue();
                }
                if (row.getCell(5) != null && StringUtils.isNotBlank(row.getCell(5).getStringCellValue())) {
                    keyword = row.getCell(5).getStringCellValue();
                }
                if (row.getCell(6) != null && StringUtils.isNotBlank(row.getCell(6).getStringCellValue())) {
                    firstAuthor = row.getCell(6).getStringCellValue();
                }
                if (row.getCell(7) != null && StringUtils.isNotBlank(row.getCell(7).getStringCellValue())) {
                    otherAuthor = row.getCell(7).getStringCellValue();
                }
                if (row.getCell(9) != null) {
                    CellType cellType = row.getCell(9).getCellTypeEnum();
                    if (null != cellType && cellType.getCode() == 1) {
                        try {
                            publicationDate = row.getCell(9).getStringCellValue();
                        } catch (Exception e) {
                            Date date = HSSFDateUtil.getJavaDate(row.getCell(9).getNumericCellValue());
                            publicationDate = DateUtils.dateToString(date, "yyyy");
                        }
                    } else {
                        row.getCell(9).setCellType(CellType.STRING);
                        publicationDate = row.getCell(9).getStringCellValue();
                    }
                }


                if (row.getCell(10) != null && StringUtils.isNotBlank(row.getCell(10).getStringCellValue())) {
                    periodical = row.getCell(10).getStringCellValue();
                }
                if (row.getCell(11) != null && StringUtils.isNotBlank(row.getCell(11).getStringCellValue())) {
                    desc = row.getCell(11).getStringCellValue();
                }
                if (row.getCell(12) != null && StringUtils.isNotBlank(row.getCell(12).getStringCellValue())) {
                    subject = row.getCell(12).getStringCellValue();
                }
                if (row.getCell(13) != null && StringUtils.isNotBlank(row.getCell(13).getStringCellValue())) {
                    instituteStandard = row.getCell(13).getStringCellValue();
                }

                Paper paper = new Paper().builder()
                        .personId(personId)
                        .languageStandard(languageStandard)
                        .build();
                if (StringUtils.isNotBlank(title)) {
                    paper.setTitle(title);
                }
                if (StringUtils.isNotBlank(doi)) {
                    paper.setDoi(doi);
                }
                if (StringUtils.isNotBlank(keyword)) {
                    paper.setKeyword(keyword);
                }
                if (StringUtils.isNotBlank(firstAuthor)) {
                    paper.setFirstAuthor(firstAuthor);
                }
                if (StringUtils.isNotBlank(otherAuthor)) {
                    paper.setOtherAuthor(otherAuthor);
                }
                if (StringUtils.isNotBlank(publicationDate)) {
                    paper.setPublicationDate(publicationDate);
                }
                if (StringUtils.isNotBlank(periodical)) {
                    paper.setPeriodical(periodical);
                }
                if (StringUtils.isNotBlank(desc)) {
                    paper.setDescribeData(desc);
                }
                list.add(paper);
            }
        } catch (IOException e) {
            log.error("parseSchoolPersonExcel 解析校领导Excel异常:{}", e);
        }
        return list;
    }

    public static void main(String[] args) {
        new ParseExcelService().parseSchoolPersonExcel("C:\\Users\\user\\Pictures\\校领导\\教育直属学校\\教育部学校领导.xlsx");
        System.out.println(  (StringUtils.isAnyBlank("1", null) ));
        String name = "陈（1）";
        name = name.replaceAll("（", "(").replaceAll("）", ")");
        name = RegexUtils.replaceData(name, RegexUtils.ALL_KUA_REGEX, "");
        System.out.println(name);
    }
}
